use XBase;
use DBI;
use Config::IniFiles;
use File::Basename;
use Spreadsheet::WriteExcel;
use Encode;

######################################################################
#               DBF文件比对
#
######################################################################
my $cfg = new Config::IniFiles(-file => "dbfCmp.ini");

print "Please input yestday mark(eg:YYYYMMDD):";
my $yestday = <STDIN>;
#$yestday = "0709";
chomp($yestday);
print "Please input today mark(eg:YYYYMMDD):";
my $today=<STDIN>;
#$today = "0709";
chomp($today);

print "Please input compare section(eg:tz,jgfymx):";
my $cmpsection = <STDIN>;
#$cmpsection = "bz";
chomp($cmpsection);

foreach my $section($cfg->Sections())
{
    $find = grep /^$section$/i, split(/,/,$cmpsection);
	next if($find == 0);
	
	my $ableftdbf = trim($cfg->val($section, 'leftdbf'));
	$ableftdbf =~ s/\{yyyymmdd\}/$yestday/g;	
	my $abrightdbf = trim($cfg->val($section, 'rightdbf'));
	$abrightdbf =~ s/\{yyyymmdd\}/$today/g;
	
	my $keys      = trim($cfg->val($section, 'keys'));
	my $fields    = trim($cfg->val($section, 'fields'));
	my $conditions = trim($cfg->val($section, 'conditions'));
	my $orders     = trim($cfg->val($section, 'orders'));
	my $resultfile     = trim($cfg->val($section, 'resultxls'));
	
	&CmpDbf($ableftdbf, $abrightdbf, $keys, $fields, $conditions, $orders, $resultfile);
}  





sub CmpDbf($ableftdbf, $abrightdbf, $keys, $fields, $conditions, $orders, $resultfile)
{
	my ($ableftdbf, $abrightdbf, $keys, $fields, $conditions, $orders, $resultfile) = @_; 

	$leftdir = dirname($ableftdbf);
	$leftdbf = basename($ableftdbf);
	$rightdir = dirname($abrightdbf);
	$rightdbf = basename($abrightdbf);
	
	my %rightHash = ();
	my %leftHash = ();

	my $leftdbh = DBI->connect("DBI:XBase:$leftdir") or die "Can't connect [$leftdir]";
	
	my $rightdbh = DBI->connect("DBI:XBase:$rightdir") or die "Can't connect [$rightdir]";
	
	$leftsql = "SELECT $keys,$fields FROM $leftdbf ";
	if($conditions ne "")
	{
		$leftsql = $leftsql ." where ".$conditions;
	}
	
	if($orders ne "")
	{
		$leftsql = $leftsql ." ". $orders;
	}
	
	print $leftsql."\n";
	
	
	my $leftsth = $leftdbh->prepare($leftsql) or die $leftdbh->errstr();
	
	$leftsth->execute() or die $leftsth->errstr();
	
#	my $cols = $leftsth->{NUM_OF_FIELDS};
#	my $colnames = $leftsth->{NAME};

	my $dest_book  = Spreadsheet::WriteExcel->new("$resultfile")  or die "Could not create a new Excel file in  : $!";

	my $dest_sheet = $dest_book->addworksheet(decode("gbk",'Result'));
	
	$diffmt = $dest_book->add_format(bold    => 1,  color   => 'red', border   => 1);
	$comfmt =  $dest_book->add_format(border   => 1);
	
	my $ixls = 0;
	
	my @key = split(/,/, $keys);
	my @field = split(/,/, $fields);
	my @newfield = (@key, @field);
	my $ifieldcnt = 0;
	
	
	
	foreach my $f(@newfield)
	{
		$dest_sheet->write($ixls, $ifieldcnt, $f, $comfmt);
		$dest_sheet->write($ixls, $ifieldcnt + @newfield, $f, $comfmt);
		$ifieldcnt++;
	}
	$ixls++;
	
	
	$rightsql = "SELECT $keys,$fields FROM $rightdbf ";
	if($conditions ne "")
	{
		$rightsql = $rightsql ." where ".$conditions;
	}
	
	if($orders ne "")
	{
		$rightsql = $rightsql ." ". $orders;
	}
	
	print $rightsql."\n";
	
	my $rightsth = $rightdbh->prepare($rightsql) or die $rightdbh->errstr();
	
	$rightsth->execute() or die $rightsth->errstr();
	
	while ($rhashref = $rightsth->fetchrow_hashref('NAME_lc'))
	{		
		my $remark = "";
		foreach my $k(@key)
		{
			$remark .= $rhashref->{&trim($k)}."@#";
			
		}		
		
		if(!exists($rightHash{$remark}))
		{
			$rightHash{$remark} = ();
			push @{$rightHash{$remark}}, $rhashref;
		}
		else
		{
			push @{$rightHash{$remark}}, $rhashref;
		}
		
		
		
	}	
	
	
	while ($hashref = $leftsth->fetchrow_hashref('NAME_lc'))
	{	
		my $remark = "";
		foreach my $k(@key)
		{
			$remark .= $hashref->{&trim($k)}."@#";
		}
		if(!exists($leftHash{$remark}))
		{
			$leftHash{$remark} = ();
			push @{$leftHash{$remark}}, $hashref;
		}
		else
		{
			push @{$leftHash{$remark}},$hashref;
		}
	
	}

	foreach my $l(keys %leftHash)
	{
		if(exists($rightHash{$l}))
		{
			#ɧ¹�¶¼ԐРͬKey,±ȽЊ�Σ¬Р̆¶ɗµŁюªР̆ѐ£¬Ǥ̼±꽇£¬خº􋤳��ۈۍ
#			print "################# [ $ixls ] $l \n";
			foreach my $lline(@{$leftHash{$l}})
			{
				my $sameCnt = 0; #±꽇£¬¼Ȃ¼Р̆ѐخ´􋻍
				my $maxCnt = 0;  #¼Ȃ¼خ´�Ǌ�	my $maxLine = (); #¼Ȃ¼Р̆ѐŚɝ
				my @complate = (); #±꽇ӑ±ȶՏጆѐ
				my $maxIdx = -1;   #±꽇ѐ̷ӽ
				my $Idx = 0;      #˽¾ދ�
				foreach my $rline(@{$rightHash{$l}})
				{
					$sameCnt = 0;
					foreach my $k(@field)
					{
						
						if($lline->{trim($k)} == $rline->{trim($k)})
						{
							$sameCnt++;
						}
					}
					if($sameCnt >= $maxCnt)
					{
						$maxLine = $rline;
						$maxCnt = $sameCnt;
						$maxIdx = $Idx;
					}
					$Idx++;
				}
				if($maxIdx > 0)
				{
					$rightHash{$l} = split(@{$rightHash{$l}}, $maxIdx);
				}
				$ifieldcnt = 0;
				foreach my $f(@newfield)
				{
					if($lline->{&trim($f)} == $maxLine->{&trim($f)})
					{
						$dest_sheet->write($ixls, $ifieldcnt, decode("gbk",$lline->{&trim($f)}), $comfmt);
						$dest_sheet->write($ixls, $ifieldcnt + @newfield, decode("gbk",$maxLine->{&trim($f)}), $comfmt);
					}
					else
					{
						$dest_sheet->write($ixls, $ifieldcnt, decode("gbk",$lline->{&trim($f)}), $diffmt);
						$dest_sheet->write($ixls, $ifieldcnt + @newfield, decode("gbk",$maxLine->{&trim($f)}), $diffmt);
					}
					$ifieldcnt++;
				}
				$ixls++;				
				
			}
		}
		else
		{
			
			foreach my $lline(@{$leftHash{$l}})
			{
				$ifieldcnt = 0;
				foreach my $f(@newfield)
				{
						
					$dest_sheet->write($ixls, $ifieldcnt, decode("gbk",$lline->{&trim($f)}), $diffmt);				
					$ifieldcnt++;
				}
				$ixls++;
			}
		}
		
	}
	
	#՚س±ࠎ޶Փ¦keyµŊ䴶
	foreach my $l(keys %rightHash)
	{
		next if(exists($leftHash{$l}));
		
		
		foreach my $lline(@{$righttHash{$l}})
		{
			$ifieldcnt = 0;
			foreach my $f(@newfield)
			{
					
				$dest_sheet->write($ixls, $ifieldcnt + @newfield , decode("gbk",$lline->{&trim($f)}), $diffmt);				
				$ifieldcnt++;
			}
			$ixls++;
		}
			
	}
	
		
	END:
	
	$dest_book->close();
	$leftdbh->disconnect;
	$rightdbh->disconnect;
	
}

sub trim
{
        my $string = shift;
        $string =~ s/^\s+//;
        $string =~ s/\s+$//;
        return $string;
}
 
         